package com.glomozda.machinerepairdao.repository.user;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import com.glomozda.machinerepairdao.domain.user.User;

import static com.glomozda.machinerepairdao.repository.jdbc.DAOJDBCUtil.*;

@SuppressWarnings({"PMD.CommentRequired", "PMD.LawOfDemeter"})
@Repository("userRepository")
public class UserRepositoryJDBC implements UserRepository {
	
	private final transient DataSource ds;

    @Autowired    
    public UserRepositoryJDBC(final DataSource ds) {
        this.ds = ds;
    }
    
    @Override
    public User find(final Integer id) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        User user = null;

        try {
            connection = ds.getConnection();
            preparedStatement = connection.prepareStatement("select * from Users where users_id=?");
            preparedStatement.setInt(1, id);
            resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
            	user = map(resultSet);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close(resultSet);
            close(preparedStatement);
            close(connection);
        }

        return user;
    }

    @Override
    public List<User> findAll() {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        final List<User> users = new ArrayList<User>();

        try {
            connection = ds.getConnection();
            preparedStatement = connection.prepareStatement("select * from Users");
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
            	users.add(map(resultSet));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close(resultSet);
            close(preparedStatement);
            close(connection);
        }

        return users;
    }

    @Override
    public boolean create(final User user) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        int userNumber = 0;
        
        try {
            connection = ds.getConnection();
            preparedStatement = connection.prepareStatement("select count (*) from Users");
            resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
            	userNumber = resultSet.getInt(1);
            }
            close(preparedStatement);
            
            preparedStatement = connection.prepareStatement("INSERT INTO Users "
                    + "(users_id, login, password, password_text) VALUES (?, ?, ?, ?)");
            preparedStatement.setInt(1, userNumber + 1);
            preparedStatement.setString(2, user.getLogin());
            preparedStatement.setString(3, user.getPassword());
            preparedStatement.setString(4, user.getPasswordText());
            preparedStatement.execute();
            return true;
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            close(preparedStatement);
            close(connection);
        }
        return false;
    }

    private User map(final ResultSet resultSet) throws SQLException {
    	final User user = new User();
    	user.setUserID(resultSet.getInt(1));
    	user.setLogin(resultSet.getString(2));
    	user.setPassword(resultSet.getString(3));
    	user.setPasswordText(resultSet.getString(4));
        return user;
    }

    @Override
    public User findByLogin(final String login) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        User user = null;

        try {
            connection = ds.getConnection();
            preparedStatement = connection.
                    prepareStatement("select * from Users where login = ?");
            preparedStatement.setString(1, login);
            resultSet = preparedStatement.executeQuery();
                      
            if (resultSet.next()) {
            	user = map(resultSet);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close(resultSet);
            close(preparedStatement);
            close(connection);
        }

        return user;
    }
}
